﻿Imports System.Data
Imports System.Data.SqlClient
Imports Conexao_liion

Partial Class ModeloContabilLinha
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

        Response.Cache.SetCacheability(HttpCacheability.ServerAndNoCache)

        If Not Page.IsPostBack Then

            Try

                LoadData()
                FillDDL()
                CarregaGVW()

            Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

        End If

    End Sub

    Private Sub CarregaGVW()

        Dim strWhere As String = ""
        Dim strDataVigencia As String

        Dim strCodTipoTributacaoII As String
        Dim strCodTipoTributacaoIPI As String
        Dim strCodTipoTributacaoPIS As String
        Dim strCodTipoTributacaoCOFINS As String
        Dim strCodTipoTributacaoICMS As String
        Dim strCodTipoTributacaoICMSST As String
        
        strCodTipoTributacaoII = ""
        strCodTipoTributacaoIPI = ""
        strCodTipoTributacaoPIS = ""
        strCodTipoTributacaoCOFINS = ""
        strCodTipoTributacaoICMS = ""
        strCodTipoTributacaoICMSST = ""

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 1, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoII = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 2, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoIPI = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 3, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoPIS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 4, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoCOFINS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 5, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoICMS = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodTipoTributacao " & _
                        "FROM TipoTributacao " & _
                        "WHERE SiglaTipoTributacao = '" & Mid(lblModeloTributario.Text, 6, 1) & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    strCodTipoTributacaoICMSST = theSqlDataReader!CodTipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        strDataVigencia = "'" & FormatMediumDate(lblDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String = "SELECT A.LinhaContabil, A.TipoModeloContabil, A.CodigoDaConta, B.DescricaoDaConta, CASE WHEN A.CredDeb = 1 THEN 'Crédito' ELSE 'Débito' END AS CredDeb, A.DescDoCampo, A.CodigoSplit, C.DescSplit " & _
                        "FROM modelocontabildatail A, planodecontas B, Split C " & _
                        "WHERE A.CodigoDaHolding = B.CodigoDaHolding AND " & _
                        "A.CodigoDaCia = B.CodigoDaCia AND " & _
                        "A.CodigoDaConta = B.CodigoDaConta AND " & _
                        "A.CodigoDaHolding = C.CodigoDaHolding AND " & _
                        "A.CodigoDaCia = C.CodigoDaCia AND " & _
                        "A.CodigoSplit = C.CodigoSplit AND " & _
                        "A.CodigoDaCia = '" & lblCia.Text & "' AND " & _
                        "A.CodUFOrigem = '" & Session("strCodigoDaUFOrigem").ToString & "' AND " & _
                        "A.CodUFDestino = '" & Session("strCodigoDaUFDestino").ToString & "' AND " & _
                        "A.CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "' AND " & _
                        "A.CodCategoriaFiscal = '" & Session("strCodigoCategoriaFiscal").ToString & "' AND " & _
                        "A.CodMeioTransporte = '" & Session("strCodigoMeioTransporte").ToString & "' AND " & _
                        "A.CodDestinacaoItem = '" & Session("strCodigoDestinacaoItem").ToString & "' AND " & _
                        "A.CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "' AND " & _
                        "A.DataVigencia = " & strDataVigencia & " AND " & _
                        "A.CodTipoTributacaoII = '" & strCodTipoTributacaoII & "' AND " & _
                        "A.CodTipoTributacaoIPI = '" & strCodTipoTributacaoIPI & "' AND " & _
                        "A.CodTipoTributacaoPIS = '" & strCodTipoTributacaoPIS & "' AND " & _
                        "A.CodTipoTributacaoCOFINS = '" & strCodTipoTributacaoCOFINS & "' AND " & _
                        "A.CodTipoTributacaoICMS = '" & strCodTipoTributacaoICMS & "' AND " & _
                        "A.CodTipoTributacaoICMSST = '" & strCodTipoTributacaoICMSST & "';"


            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                mydatatable.Load(theSqlDataReader)
                gvwData.DataSource = mydatatable
                gvwData.DataBind()
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = "Msg DB: " & ex.Message
            End Try

        End Using

    End Sub

    Private Sub LoadData()

        Dim strDataVigencia As String

        Dim strCodTipoTributacaoII As String
        Dim strCodTipoTributacaoIPI As String
        Dim strCodTipoTributacaoPIS As String
        Dim strCodTipoTributacaoCOFINS As String
        Dim strCodTipoTributacaoICMS As String
        Dim strCodTipoTributacaoICMSST As String

        strCodTipoTributacaoII = ""
        strCodTipoTributacaoIPI = ""
        strCodTipoTributacaoPIS = ""
        strCodTipoTributacaoCOFINS = ""
        strCodTipoTributacaoICMS = ""
        strCodTipoTributacaoICMSST = ""

        lblHolding.Text = Session("strCodigoDaHolding").ToString
        lblCia.Text = Session("strCodigoDaCia").ToString
        lblNaturezaOpercao.Text = Session("strCodNaturezaOperacao").ToString
        lblUFOrigem.Text = Session("strSiglaDaUFOrigem")
        lblUFDestino.Text = Session("strSiglaDaUFDestino")
        lblCategoriaFiscal.Text = Session("strDscCategoriaFiscal")
        lblMeioTransporte.Text = Session("strDscMeioTransporte")
        lblOrigemItem.Text = Session("intCodigoDaOrigemDoItem")
        lblDestinacaoItem.Text = Session("strDscDestinacaoItem")
        lblDataVigencia.Text = Session("strDataVigencia").ToString

        strDataVigencia = "'" & FormatMediumDate(lblDataVigencia.Text) & "'" 'Format(CDate(txtDataVigencia.Text), "yyyy-MM-dd HH:mm:ss")

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim theQueryString As String = "SELECT" & _
                     " A.CodUFOrigem," & _
                     " A.CodUFDestino," & _
                     " A.CodCategoriaFiscal," & _
                     " A.CodMeioTransporte," & _
                     " A.CodDestinacaoItem," & _
                     " A.CodigoDaOrigemDoItem," & _
                     " A.DataVigencia,"

            theQueryString = theQueryString & _
                     " A.CodTipoTributacaoII," & _
                     " A.CodTipoTributacaoIPI," & _
                     " A.CodTipoTributacaoPIS," & _
                     " A.CodTipoTributacaoCOFINS," & _
                     " A.CodTipoTributacaoICMS," & _
                     " A.CodTipoTributacaoICMSST"


            theQueryString = theQueryString & _
                     " FROM ModeloContabilHeader A, uf B, uf C, categoriafiscal D, meiotransporte E, destinacaoitem F" & _
                     " WHERE A.CodUFOrigem = B.CodigoDaUF AND A.CodUFDestino = C.CodigoDaUF AND A.CodCategoriaFiscal = D.CodCategoriaFiscal AND A.CodMeioTransporte = E.CodMeioTransporte AND A.CodDestinacaoItem = F.CodDestinacaoItem AND A.CodigoDaHolding = '" & lblHolding.Text & "'" & _
                     " AND A.CodigoDaCia = '" & lblCia.Text & "'" & _
                     " AND B.SiglaDaUF = '" & Session("strSiglaDaUFOrigem").ToString & "'" & _
                     " AND C.SiglaDaUF = '" & Session("strSiglaDaUFDestino").ToString & "'" & _
                     " AND A.CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao").ToString & "'" & _
                     " AND D.DscCategoriaFiscal = '" & Session("strDscCategoriaFiscal").ToString & "'" & _
                     " AND E.DscMeioTransporte = '" & Session("strDscMeioTransporte").ToString & "'" & _
                     " AND F.DscDestinacaoItem = '" & Session("strDscDestinacaoItem").ToString & "'" & _
                     " AND A.CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem").ToString & "'" & _
                     " AND A.DataVigencia = " & strDataVigencia & ";"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try

                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                Do While theSqlDataReader.Read()

                    'ddlUFOrigem.SelectedValue = theSqlDataReader!CodUFOrigem
                    'ddlUFDestino.SelectedValue = theSqlDataReader!CodUFDestino
                    'ddlCategoriaFiscal.SelectedValue = theSqlDataReader!CodCategoriaFiscal
                    'ddlMeioTransporte.SelectedValue = theSqlDataReader!CodMeioTransporte
                    'ddlDestinacaoItem.SelectedValue = theSqlDataReader!CodDestinacaoItem
                    'ddlOrigemItem.SelectedValue = theSqlDataReader!CodigoDaOrigemDoItem

                    strCodTipoTributacaoII = theSqlDataReader!CodTipoTributacaoII
                    strCodTipoTributacaoIPI = theSqlDataReader!CodTipoTributacaoIPI
                    strCodTipoTributacaoPIS = theSqlDataReader!CodTipoTributacaoPIS
                    strCodTipoTributacaoCOFINS = theSqlDataReader!CodTipoTributacaoCOFINS
                    strCodTipoTributacaoICMS = theSqlDataReader!CodTipoTributacaoICMS
                    strCodTipoTributacaoICMSST = theSqlDataReader!CodTipoTributacaoICMSST

                Loop

                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim mydatatable As New DataTable
            Dim theQueryString As String
            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " WHERE CodTipoTributacao  = " & strCodTipoTributacaoII & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    lblModeloTributario.Text = theSqlDataReader!TipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim mydatatable As New DataTable
            Dim theQueryString As String
            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " WHERE CodTipoTributacao  = " & strCodTipoTributacaoIPI & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim mydatatable As New DataTable
            Dim theQueryString As String
            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " WHERE CodTipoTributacao  = " & strCodTipoTributacaoPIS & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim mydatatable As New DataTable
            Dim theQueryString As String
            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " WHERE CodTipoTributacao  = " & strCodTipoTributacaoCOFINS & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim mydatatable As New DataTable
            Dim theQueryString As String
            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " WHERE CodTipoTributacao  = " & strCodTipoTributacaoICMS & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim mydatatable As New DataTable
            Dim theQueryString As String
            theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao AS TipoTributacao" & _
                         " FROM TipoTributacao" & _
                         " WHERE CodTipoTributacao  = " & strCodTipoTributacaoICMSST & ";"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    lblModeloTributario.Text = lblModeloTributario.Text & theSqlDataReader!TipoTributacao
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try
        End Using







        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodigoDaUF " & _
                        "FROM uf " & _
                        "WHERE SiglaDaUF = '" & Session("strSiglaDaUFOrigem") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strCodigoDaUFOrigem", theSqlDataReader!CodigoDaUF)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodigoDaUF " & _
                        "FROM uf " & _
                        "WHERE SiglaDaUF = '" & Session("strSiglaDaUFDestino") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strCodigoDaUFDestino", theSqlDataReader!CodigoDaUF)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT DscNaturezaOperacao " & _
                        "FROM NaturezaOperacao " & _
                        "WHERE CodNaturezaOperacao = '" & Session("strCodNaturezaOperacao") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strDescNaturezaOperacao", theSqlDataReader!DscNaturezaOperacao)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodCategoriaFiscal " & _
                        "FROM categoriafiscal " & _
                        "WHERE DscCategoriaFiscal = '" & Session("strDscCategoriaFiscal") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strCodigoCategoriaFiscal", theSqlDataReader!CodCategoriaFiscal)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodMeioTransporte " & _
                        "FROM MeioTransporte " & _
                        "WHERE DscMeioTransporte = '" & Session("strDscMeioTransporte") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strCodigoMeioTransporte", theSqlDataReader!CodMeioTransporte)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT DescricaoDaOrigemDoItem " & _
                        "FROM OrigemDoItem " & _
                        "WHERE CodigoDaOrigemDoItem = '" & Session("intCodigoDaOrigemDoItem") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strDescOrigemDoItem", theSqlDataReader!DescricaoDaOrigemDoItem)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)
            Dim theQueryString As String = "SELECT CodDestinacaoItem " & _
                        "FROM DestinacaoItem " & _
                        "WHERE DscDestinacaoItem = '" & Session("strDscDestinacaoItem") & "'"
            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString
            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()
                If (theSqlDataReader.Read()) Then
                    Session.Add("strCodigoDestinacaoItem", theSqlDataReader!CodDestinacaoItem)
                End If
                theSqlDataReader.Close()
            Catch ex As Exception
                'theSqlDataReader.Close()
                lblMsg.Text = ex.Message
            End Try
        End Using


    End Sub

    Private Sub FillDDL()

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaUF, DescricaoDaUF" & vbNewLine & _
                         " FROM UF" & vbNewLine & _
                         " WHERE SiglaDaUF = '" & lblUFOrigem.Text & "' ORDER BY SiglaDaUF;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    lblUFOrigem.Text = lblUFOrigem.Text & " - " & theSqlDataReader!DescricaoDaUF
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using


        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodigoDaUF, DescricaoDaUF" & vbNewLine & _
                         " FROM UF" & vbNewLine & _
                         " WHERE SiglaDaUF = '" & lblUFDestino.Text & "' ORDER BY SiglaDaUF;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    lblUFDestino.Text = lblUFDestino.Text & " - " & theSqlDataReader!DescricaoDaUF
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using

        Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

            Dim mydatatable As New DataTable
            Dim theQueryString As String

            theQueryString = "SELECT CodNaturezaOperacao," & _
                     " DscNaturezaOperacao AS NaturezaOperacao" & vbNewLine & _
                     " FROM NaturezaOperacao" & _
                     " WHERE CodNaturezaOperacao = '" & lblNaturezaOpercao.Text & "'" & _
                     " ORDER BY CodNaturezaOperacao;"

            Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
            theSqlCommand.CommandText = theQueryString

            Try
                theSqlConnection.Open()
                Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

                If (theSqlDataReader.Read()) Then
                    lblNaturezaOpercao.Text = lblNaturezaOpercao.Text & " - " & theSqlDataReader!NaturezaOperacao
                End If

                theSqlDataReader.Close()
            Catch ex As Exception
                lblMsg.Text = ex.Message
            End Try

        End Using






        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String


        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlNaturezaOperacao.DataTextField = "NaturezaOperacao"
        '        ddlNaturezaOperacao.DataValueField = "CodNaturezaOperacao"
        '        ddlNaturezaOperacao.DataSource = mydatatable
        '        ddlNaturezaOperacao.DataBind()

        '        ddlNaturezaOperacao.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using


        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String

        '    theQueryString = "SELECT CodCategoriaFiscal, DscCategoriaFiscal" & _
        '                 " FROM CategoriaFiscal" & _
        '                 " ORDER BY DscCategoriaFiscal;"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlCategoriaFiscal.DataTextField = "DscCategoriaFiscal"
        '        ddlCategoriaFiscal.DataValueField = "CodCategoriaFiscal"
        '        ddlCategoriaFiscal.DataSource = mydatatable
        '        ddlCategoriaFiscal.DataBind()

        '        ddlCategoriaFiscal.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String

        '    theQueryString = "SELECT CodMeioTransporte, DscMeioTransporte" & _
        '                 " FROM MeioTransporte" & _
        '                 " ORDER BY DscMeioTransporte;"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlMeioTransporte.DataTextField = "DscMeioTransporte"
        '        ddlMeioTransporte.DataValueField = "CodMeioTransporte"
        '        ddlMeioTransporte.DataSource = mydatatable
        '        ddlMeioTransporte.DataBind()

        '        ddlMeioTransporte.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using


        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String

        '    theQueryString = "SELECT CodigoDaOrigemDoItem, DescricaoDaOrigemDoItem" & _
        '                 " FROM OrigemDoItem" & _
        '                 " ORDER BY CodigoDaOrigemDoItem;"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlOrigemItem.DataTextField = "DescricaoDaOrigemDoItem"
        '        ddlOrigemItem.DataValueField = "CodigoDaOrigemDoItem"
        '        ddlOrigemItem.DataSource = mydatatable
        '        ddlOrigemItem.DataBind()

        '        ddlOrigemItem.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using


        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String

        '    theQueryString = "SELECT CodDestinacaoItem, DscDestinacaoItem" & _
        '                 " FROM DestinacaoItem" & _
        '                 " ORDER BY DscDestinacaoItem;"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlDestinacaoItem.DataTextField = "DscDestinacaoItem"
        '        ddlDestinacaoItem.DataValueField = "CodDestinacaoItem"
        '        ddlDestinacaoItem.DataSource = mydatatable
        '        ddlDestinacaoItem.DataBind()

        '        ddlDestinacaoItem.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using


        'Using theSqlConnection As New SqlConnection(Conexao_liion.theConnectionString)

        '    Dim mydatatable As New DataTable
        '    Dim theQueryString As String

        '    theQueryString = "SELECT CodTipoTributacao, SiglaTipoTributacao + ' - ' + DscTipoTributacao AS TipoTributacao" & _
        '                 " FROM TipoTributacao" & _
        '                 " ORDER BY CodTipoTributacao;"

        '    Dim theSqlCommand As SqlCommand = theSqlConnection.CreateCommand()
        '    theSqlCommand.CommandText = theQueryString

        '    Try
        '        theSqlConnection.Open()
        '        Dim theSqlDataReader As SqlDataReader = theSqlCommand.ExecuteReader()

        '        mydatatable.Load(theSqlDataReader)
        '        ddlTipoTributacaoII.DataTextField = "TipoTributacao"
        '        ddlTipoTributacaoII.DataValueField = "CodTipoTributacao"
        '        ddlTipoTributacaoII.DataSource = mydatatable
        '        ddlTipoTributacaoII.DataBind()

        '        ddlTipoTributacaoII.Items.Insert(0, "")

        '        ddlTipoTributacaoIPI.DataTextField = "TipoTributacao"
        '        ddlTipoTributacaoIPI.DataValueField = "CodTipoTributacao"
        '        ddlTipoTributacaoIPI.DataSource = mydatatable
        '        ddlTipoTributacaoIPI.DataBind()

        '        ddlTipoTributacaoIPI.Items.Insert(0, "")

        '        ddlTipoTributacaoPIS.DataTextField = "TipoTributacao"
        '        ddlTipoTributacaoPIS.DataValueField = "CodTipoTributacao"
        '        ddlTipoTributacaoPIS.DataSource = mydatatable
        '        ddlTipoTributacaoPIS.DataBind()

        '        ddlTipoTributacaoPIS.Items.Insert(0, "")

        '        ddlTipoTributacaoCOFINS.DataTextField = "TipoTributacao"
        '        ddlTipoTributacaoCOFINS.DataValueField = "CodTipoTributacao"
        '        ddlTipoTributacaoCOFINS.DataSource = mydatatable
        '        ddlTipoTributacaoCOFINS.DataBind()

        '        ddlTipoTributacaoCOFINS.Items.Insert(0, "")

        '        ddlTipoTributacaoICMS.DataTextField = "TipoTributacao"
        '        ddlTipoTributacaoICMS.DataValueField = "CodTipoTributacao"
        '        ddlTipoTributacaoICMS.DataSource = mydatatable
        '        ddlTipoTributacaoICMS.DataBind()

        '        ddlTipoTributacaoICMS.Items.Insert(0, "")

        '        ddlTipoTributacaoICMSST.DataTextField = "TipoTributacao"
        '        ddlTipoTributacaoICMSST.DataValueField = "CodTipoTributacao"
        '        ddlTipoTributacaoICMSST.DataSource = mydatatable
        '        ddlTipoTributacaoICMSST.DataBind()

        '        ddlTipoTributacaoICMSST.Items.Insert(0, "")

        '        theSqlDataReader.Close()
        '    Catch ex As Exception
        '        'theSqlDataReader.Close()
        '        lblMsg.Text = "Msg DB: " & ex.Message
        '    End Try

        'End Using

    End Sub

    Protected Sub btnVoltar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnVoltar.Click
        Response.Redirect("ModeloContabil.aspx")
    End Sub

    Public Function FormatMediumDate(ByVal DateValue)

        Dim strYYYY
        Dim strMM
        Dim strDD

        'Strip the Year
        strYYYY = CStr(DatePart("yyyy", DateValue))

        'Strip the Month	
        strMM = CStr(DatePart("m", DateValue))

        'See if month is a single digit if so display it as 01, 02, 03, etc
        If Len(strMM) = 1 Then strMM = "0" & strMM

        'Strip the Date	
        strDD = CStr(DatePart("d", DateValue))

        'See if the Day is a single digit day if so diaply it as 01, 02, 03, etc
        If Len(strDD) = 1 Then strDD = "0" & strDD

        'return the date formatted as DD/MM/YYY	
        FormatMediumDate = strYYYY & "/" & strMM & "/" & strDD

        'you could return the date many ways just retype the above string
        'FormatMediumDate = strDD & "-" & strMM & "-" & strYYYY
        'FormatMediumDate = strMM & "/" & strDD & "/" & strYYYY
        'FormatMediumDate = strYYYY & "-" & strMM & "-" & strMM

    End Function

    Protected Sub btnIncluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncluir.Click

        Session.Add("strOperacao", "I") ' Incluir

        If (OptModelo.SelectedIndex = 0) Then
            Session.Add("strTipoModeloContabil", "DID")
        ElseIf (OptModelo.SelectedIndex = 1) Then
            Session.Add("strTipoModeloContabil", "NFS")
         ElseIf (OptModelo.SelectedIndex = 2) Then
            Session.Add("strTipoModeloContabil", "CDD")
        ElseIf (OptModelo.SelectedIndex = 3) Then
            Session.Add("strTipoModeloContabil", "NFE")
        ElseIf (OptModelo.SelectedIndex = 4) Then
            Session.Add("strTipoModeloContabil", "SNF")
        End If
        Response.Redirect("ModeloContabilDI.aspx")


    End Sub

    Protected Sub OptModelo_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles OptModelo.SelectedIndexChanged
        btnIncluir.Enabled = True
    End Sub

    Protected Sub gvwData_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles gvwData.PageIndexChanging

        Try
            CarregaGVW()

            gvwData.PageIndex = e.NewPageIndex
            gvwData.DataBind()

        Catch ex As Exception
            lblMsg.Text = ex.Message
        End Try

    End Sub

    Protected Sub gvwData_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles gvwData.SelectedIndexChanged

        If (gvwData.SelectedRow.Cells(1).Text = "DID") Then
            OptModelo.SelectedIndex = 0
        ElseIf (gvwData.SelectedRow.Cells(1).Text = "NFS") Then
            OptModelo.SelectedIndex = 1
        ElseIf (gvwData.SelectedRow.Cells(1).Text = "CDD") Then
            OptModelo.SelectedIndex = 2
        ElseIf (gvwData.SelectedRow.Cells(1).Text = "NFE") Then
            OptModelo.SelectedIndex = 3
        ElseIf (gvwData.SelectedRow.Cells(1).Text = "SNF") Then
            OptModelo.SelectedIndex = 4
        End If


        'If Not Page.IsPostBack Then
        BtnConsultar.Enabled = True
        btnAlterar.Enabled = True
        btnExcluir.Enabled = True
        'End If

    End Sub

    Protected Sub btnAlterar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnAlterar.Click

        Session.Add("strOperacao", "A") ' Incluir

        If (OptModelo.SelectedIndex = 0) Then
            Session.Add("strTipoModeloContabil", "DID")
        ElseIf (OptModelo.SelectedIndex = 1) Then
            Session.Add("strTipoModeloContabil", "NFS")
        ElseIf (OptModelo.SelectedIndex = 2) Then
            Session.Add("strTipoModeloContabil", "CDD")
        ElseIf (OptModelo.SelectedIndex = 3) Then
            Session.Add("strTipoModeloContabil", "NFE")
        ElseIf (OptModelo.SelectedIndex = 4) Then
            Session.Add("strTipoModeloContabil", "SNF")
        End If
        Session.Add("strLinhaModeloContabil", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strTipoModeloContabil", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))


        Response.Redirect("ModeloContabilDI.aspx")

    End Sub


    Protected Sub btnExcluir_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnExcluir.Click

        Session.Add("strOperacao", "E") ' Incluir

        If (OptModelo.SelectedIndex = 0) Then
            Session.Add("strTipoModeloContabil", "DID")
        ElseIf (OptModelo.SelectedIndex = 1) Then
            Session.Add("strTipoModeloContabil", "NFS")
        ElseIf (OptModelo.SelectedIndex = 2) Then
            Session.Add("strTipoModeloContabil", "CDD")
        ElseIf (OptModelo.SelectedIndex = 3) Then
            Session.Add("strTipoModeloContabil", "NFE")
        ElseIf (OptModelo.SelectedIndex = 4) Then
            Session.Add("strTipoModeloContabil", "SNF")
        End If
        Session.Add("strLinhaModeloContabil", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strTipoModeloContabil", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))


        Response.Redirect("ModeloContabilDI.aspx")

    End Sub

    Protected Sub BtnConsultar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles BtnConsultar.Click

        Session.Add("strOperacao", "C") ' Incluir

        If (OptModelo.SelectedIndex = 0) Then
            Session.Add("strTipoModeloContabil", "DID")
        ElseIf (OptModelo.SelectedIndex = 1) Then
            Session.Add("strTipoModeloContabil", "NFS")
        ElseIf (OptModelo.SelectedIndex = 2) Then
            Session.Add("strTipoModeloContabil", "CDD")
        ElseIf (OptModelo.SelectedIndex = 3) Then
            Session.Add("strTipoModeloContabil", "NFE")
        ElseIf (OptModelo.SelectedIndex = 4) Then
            Session.Add("strTipoModeloContabil", "SNF")
        End If
        Session.Add("strLinhaModeloContabil", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(0).Text))
        Session.Add("strTipoModeloContabil", HttpUtility.HtmlDecode(gvwData.SelectedRow.Cells(1).Text))


        Response.Redirect("ModeloContabilDI.aspx")

    End Sub
End Class
